package com.wuseguang.springcloud.service.impl;

import com.wuseguang.springcloud.dao.jdbc.AchievementJdbc;
import com.wuseguang.springcloud.util.PaginationUtil;
import com.wuseguang.springcloud.util.PagingTool;
import com.wuseguang.springcloud.util.Result;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;

@Service
public class AchievementServiceImpl implements com.wuseguang.springcloud.service.AchievementService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private AchievementJdbc achievementJdbc;


    /**
     * @描述 教师端小程序（成绩查询）
     * @创建人 zm
     * @创建时间 2020/6/24
     */
    @Override
    public Result querySchoolAchievementAll(String admin, String schoolId, String teacherId, Integer indexNo, Integer rows) {
        List<Map<String, Object>> list = null;
        if ("0".equals(admin)) {
            String sql = "  SELECT t.name,t.head_portrait,s.id,s.classname,s.achievement_name,DATE_FORMAT(s.opertime ,'%Y-%m-%d %H:%i') opertime,s.type FROM school_achievement s LEFT JOIN  teacher t ON (s.teacher_id=t.id) WHERE s.school_id=? order by s.opertime desc";
            list = achievementJdbc.queryListMap(sql, schoolId);
        } else {
            String classIds = achievementJdbc.findClassIds(schoolId, teacherId);
            String sql = "  SELECT t.name,t.head_portrait,s.id,s.classname,s.achievement_name,DATE_FORMAT(s.opertime ,'%Y-%m-%d %H:%i') opertime,s.type FROM school_achievement s LEFT JOIN  teacher t ON (s.teacher_id=t.id)  WHERE s.class_id IN (" + classIds + ")";
            list = achievementJdbc.queryListMap(sql);
        }
        if (list != null && list.size() > 0) {
            String sql = " SELECT   0 AS id,'等第' AS course_name  FROM  DUAL UNION ALL SELECT   id,course_name  FROM school_achievement_course WHERE achievement_id=? AND state=1";
            String sql1 = "SELECT  t.fraction FROM school_achievement_course s,student_achievement t WHERE s.id=t.achievement_course_id AND  s.achievement_id=? GROUP BY t.fraction ORDER BY  CONVERT(t.fraction USING gbk)";
            String sql3 = "SELECT ROUND(AVG(fraction),2) string FROM ( SELECT SUM(a.fraction+0) fraction FROM  school_achievement_student o  LEFT JOIN  school_achievement_course c  ON (o.achievement_id=c.achievement_id AND c.state=1 )  " +
                    "   LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND o.student_id=a.student_id)     WHERE  o.achievement_id=? GROUP BY a.student_id )t";
            String sql4 = " SELECT t.student_name,(SELECT SUM(a.fraction+0) fraction FROM   student_achievement a WHERE t.student_id=a.student_id AND  a.achievement_course_id IN ( SELECT id FROM school_achievement_course WHERE achievement_id=t.achievement_id)) count FROM    school_achievement_student t WHERE t.achievement_id=?  ORDER BY count desc LIMIT 0,3";
            for (Map<String, Object> map : list) {
                Object id = map.get("id");
                Object type = map.get("type");
                if ("1".equals(type.toString())) {//等第制
                    List<String> strings = achievementJdbc.queryListString(sql1, new Object[]{id});
                    String s = "";
                    for (int i = 0; i < strings.size(); i++) {
                        s += "count(case when t.fraction='" + strings.get(i) + "' then 1 end) as '" + strings.get(i) + "',";
                    }
                    s = s.substring(0, s.length() - 1);
                    String sql2 = " select   " + s + "  from  student_achievement t where t.achievement_course_id=?  ORDER BY  CONVERT(t.fraction USING gbk)";
                    List<Map<String, Object>> listOne = achievementJdbc.queryListMap(sql, id);
                    for (Map<String, Object> mapOne : listOne) {
                        Object id1 = mapOne.get("id");
                        if ("0".equals(id1.toString())) {
                            mapOne.put("list", strings);
                        } else {
                            List<String> strings1 = new ArrayList<>();
                            Map<String, Object> map2 = achievementJdbc.queryMap(sql2, id1);
                            for (Object s2 : map2.values()) {//遍历map的值
                                strings1.add(s2 + "");
                            }
                            mapOne.put("list", strings1);
                        }
                    }
                    map.put("list", listOne);
                } else {//分数制
                    List<Map<String, Object>> list1 = achievementJdbc.queryListMap(sql4, id);
                    String s = achievementJdbc.queryString(sql3, id);
                    map.put("avg", s);
                    map.put("list", list1);
                }
            }
        }
        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        //返回数据
        return new Result(new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo));
    }


    /**
     * @描述 （教师小程序）成绩页详情
     * @创建人 zm
     * @创建时间 2020/6/24
     */
    @Override
    public Result querySchoolAchievementById(Integer id, Integer type) {
        Map<String, Object> map = new HashMap<>();
        try {
            String sql2 = " SELECT  t.name,s.type,s.achievement_name,s.classname ,s.id,s.class_id,(SELECT COUNT(1) FROM school_achievement_student WHERE achievement_id=s.id) count,DATE_FORMAT(s.opertime,'%Y-%m-%d %H:%i') opertime FROM school_achievement s LEFT JOIN  teacher t ON (s.teacher_id=t.id)  WHERE  s.id=? ";
            map = achievementJdbc.queryMap(sql2, id);
            if (type == 0) {//分数制
                String sql = " SELECT c.total_score sum,c.course_name,ROUND(AVG(a.fraction+0),2) avg,c.id FROM  school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                        "   LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)  WHERE c.state=1 AND s.achievement_id=? GROUP BY c.id ORDER BY c.opertime,c.id ";
                List<Map<String, Object>> maps = achievementJdbc.queryListMap(sql, id);
                map.put("statisticsList", maps);
            } else {
                String sql = " SELECT   0 AS id,'等第' AS course_name  FROM  DUAL UNION ALL SELECT   id,course_name  FROM school_achievement_course WHERE achievement_id=? AND state=1";
                String sql1 = "SELECT  t.fraction FROM school_achievement_course s,student_achievement t WHERE s.id=t.achievement_course_id AND  s.achievement_id=? GROUP BY t.fraction ORDER BY  CONVERT(t.fraction USING gbk)";
                List<String> strings = achievementJdbc.queryListString(sql1, new Object[]{id});
                String s = "";
                for (int i = 0; i < strings.size(); i++) {
                    s += "count(case when t.fraction='" + strings.get(i) + "' then 1 end) as '" + strings.get(i) + "',";
                }
                s = s.substring(0, s.length() - 1);
                sql2 = "select   " + s + "  from  student_achievement t where t.achievement_course_id=?  ORDER BY  CONVERT(t.fraction USING gbk)";
                List<Map<String, Object>> listOne = achievementJdbc.queryListMap(sql, id);
                for (Map<String, Object> mapOne : listOne) {
                    Object id1 = mapOne.get("id");
                    if ("0".equals(id1.toString())) {
                        mapOne.put("list", strings);
                    } else {
                        List<String> strings1 = new ArrayList<>();
                        Map<String, Object> map2 = achievementJdbc.queryMap(sql2, id1);
                        for (Object s2 : map2.values()) {//遍历map的值
                            strings1.add(s2 + "");
                        }
                        mapOne.put("list", strings1);
                    }
                }
                map.put("statisticsList", listOne);
            }

            String sql = "SELECT c.id,a.fraction,c.course_name FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                    " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? GROUP BY c.id ORDER BY c.opertime,c.id";
            if ("0".equals(map.get("type").toString())) {
                sql = " (SELECT c.id,a.fraction,c.course_name FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                        " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? GROUP BY c.id ORDER BY c.opertime,c.id LIMIT 0, 10000)" +
                        "  UNION ALL (SELECT  '0',SUM(a.fraction+0) fraction,'总分' course_name FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                        "    LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=? ) ";
            }
            String sql1 = "SELECT t.student_id,t.student_name  FROM    school_achievement_student t WHERE t.achievement_id=?  ORDER BY (SELECT SUM(a.fraction+0) fraction FROM   student_achievement a WHERE t.student_id=a.student_id AND a.achievement_course_id IN ( SELECT id FROM school_achievement_course WHERE achievement_id=t.achievement_id) ) DESC";
            List<Map<String, Object>> maps1 = achievementJdbc.queryListMap(sql1, id);
            if (maps1 != null && maps1.size() > 0) {
                for (int i = 0; i < maps1.size(); i++) {
                    Map<String, Object> map1 = maps1.get(i);
                    Object student_id = map1.get("student_id");
                    if ("0".equals(map.get("type").toString())) {
                        map1.put("list", achievementJdbc.queryListMap(sql, id, student_id, id, student_id));
                    } else {
                        map1.put("list", achievementJdbc.queryListMap(sql, id, student_id));
                    }
                }
            }
            map.put("detailsList", maps1);
        } catch (Exception e) {
            logger.info("querySchoolAchievementById,erorr={}", e);
        }
        return new Result(map);
    }

}
